// Copyright (c) Appeon. All rights reserved.
// Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. 
// You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using JetBrains.Annotations;
using SnapObjects.Commons;

namespace SnapObjects.Data
{
    internal class AdoDbCommand : IAdoDbCommand
    {
        public AdoDbCommand(
            [NotNull] string sqlText,
            IEnumerable<IAdoDbParameter> parameters,
            bool addExceptionToTransaction,
            CommandType commandType = CommandType.Text)
        {
            Check.NotNull(sqlText, nameof(sqlText));

            this.SqlText = sqlText;
            this.Parameters = parameters;
            this.CommandType = commandType;
            this.AddExceptionToTransaction = addExceptionToTransaction;
        }

        public virtual string SqlText { get; set; }

        public virtual IEnumerable<IAdoDbParameter> Parameters { get; set; }

        public CommandType CommandType { get; }

        public CommandBehavior CommandBehavior { get; set; }

        public bool AddExceptionToTransaction { get; }

        public virtual Tuple<DbCommand, DbDataReader> ExecuteQuery(
            DataContext dataContext, CommandBehavior commandBehavior = CommandBehavior.Default)
        {
            return (Tuple<DbCommand, DbDataReader>)this.Execute(
                dataContext, AdoDbCommandMethod.ExecuteReader, commandBehavior);
        }

        public async virtual Task<Tuple<DbCommand, DbDataReader>> ExecuteQueryAsync(
            DataContext dataContext,
            CommandBehavior commandBehavior = CommandBehavior.Default,
            CancellationToken cancellationToken = default)
        {
            var dbCommand = this.CreateCommand(dataContext);

            TrackLogger.ExcuteSqlLog(dbCommand);

            try
            {
                var reader = await dbCommand.ExecuteReaderAsync(commandBehavior, cancellationToken);

                this.ConfigDbDataReader(reader, dataContext.ContextOptions);

                var result = new Tuple<DbCommand, DbDataReader>(dbCommand, reader);

                return result;
            }
            catch (Exception ex)
            {
                if (dataContext.CurrentTransaction is AdoDbTransaction transaction && this.AddExceptionToTransaction)
                {
                    transaction.LogException(ex);
                }

                throw new DbExecuteException(ex, dbCommand.CommandText, this.GetSqlDbCode(ex));
            }
        }

        public virtual int ExecuteNonQuery(DataContext dataContext)
        {
            int ret;

            dataContext.CurrentConnection.Open();

            try
            {
                ret = (int)this.Execute(dataContext, AdoDbCommandMethod.ExecuteNonQuery);
            }
            finally
            {
                dataContext.CurrentConnection.Close();
            }

            return ret;
        }

        public async virtual Task<int> ExecuteNonQueryAsync(
            DataContext dataContext,
            CancellationToken cancellationToken = default)
        {
            await dataContext.CurrentConnection.OpenAsync(cancellationToken);

            var dbCommand = this.CreateCommand(dataContext);

            TrackLogger.ExcuteSqlLog(dbCommand);

            try
            {
                var task = await dbCommand.ExecuteNonQueryAsync(cancellationToken);
                
                this.SetOutputParameterValue(
                    this.Parameters, dbCommand.Parameters, dataContext.ContextOptions, dataContext.AdoDbSqlProvider.SqlGenerationHelper);

                return task;
            }
            catch (Exception ex)
            {
                if (dataContext.CurrentTransaction is AdoDbTransaction transaction && this.AddExceptionToTransaction)
                {
                    transaction.LogException(ex);
                }

                throw new DbExecuteException(ex, dbCommand.CommandText, this.GetSqlDbCode(ex));
            }
            finally
            {
                dbCommand.Parameters?.Clear();

                await dbCommand.DisposeAsync();

                await dataContext.CurrentConnection.CloseAsync();
            }
        }

        public virtual object ExecuteScalar(DataContext dataContext)
        {
            object ret;

            dataContext.CurrentConnection.Open();

            try
            {
                ret = this.Execute(dataContext, AdoDbCommandMethod.ExecuteScalar);
            }
            finally
            {
                dataContext.CurrentConnection.Close();
            }

            return ret;
        }

        public virtual async Task<object> ExecuteScalarAsync(
            DataContext dataContext,
            CancellationToken cancellationToken = default)
        {
            await dataContext.CurrentConnection.OpenAsync(cancellationToken);

            var dbCommand = this.CreateCommand(dataContext);

            TrackLogger.ExcuteSqlLog(dbCommand);

            try
            {
                var result = await dbCommand.ExecuteScalarAsync(cancellationToken);

                if (result?.GetType() == typeof(DBNull))
                {
                    result = null;
                }

                this.SetOutputParameterValue(
                    this.Parameters, dbCommand.Parameters, dataContext.ContextOptions, dataContext.AdoDbSqlProvider.SqlGenerationHelper);

                return result;
            }
            catch (Exception ex)
            {
                if (dataContext.CurrentTransaction is AdoDbTransaction transaction && this.AddExceptionToTransaction)
                {
                    transaction.LogException(ex);
                }

                throw new DbExecuteException(ex, dbCommand.CommandText, this.GetSqlDbCode(ex));
            }
            finally
            {
                dbCommand.Parameters?.Clear();

                await dbCommand.DisposeAsync();

                await dataContext.CurrentConnection.CloseAsync();
            }
        }

        public virtual void ExecuteParseOnly(DataContext dataContext)
        {
            var command = this.CreateCommand(dataContext);

            command.CommandText = this.BuildNoExecCommandText(dataContext, command.CommandText);

            command.ExecuteNonQuery();
        }

        public virtual Task ExecuteParseOnlyAsync(
            DataContext dataContext, CancellationToken cancellationToken = default)
        {
            var command = this.CreateCommand(dataContext);

            command.CommandText = this.BuildNoExecCommandText(dataContext, command.CommandText);

            return command.ExecuteNonQueryAsync(cancellationToken);
        }

        protected virtual string BuildNoExecCommandText(DataContext context, string commandText)
        {
            return commandText;
        }

        protected virtual object Execute(
            DataContext dataContext,
            AdoDbCommandMethod executeMethod,
            CommandBehavior commandBehavior = CommandBehavior.Default)
        {
            var dbCommand = this.CreateCommand(dataContext);

            TrackLogger.ExcuteSqlLog(dbCommand);

            object result;
            var readerOpen = false;
            try
            {
                switch (executeMethod)
                {
                    case AdoDbCommandMethod.ExecuteNonQuery:
                    {
                        result = dbCommand.ExecuteNonQuery();

                        break;
                    }
                    case AdoDbCommandMethod.ExecuteScalar:
                    {
                        result = dbCommand.ExecuteScalar();

                        if (result?.GetType() == typeof(DBNull))
                        {
                            result = null;
                        }

                        break;
                    }
                    case AdoDbCommandMethod.ExecuteReader:
                    {
                        var reader = dbCommand.ExecuteReader(commandBehavior);

                        this.ConfigDbDataReader(reader, dataContext.ContextOptions);

                        result = new Tuple<DbCommand, DbDataReader>(dbCommand, reader);
                        readerOpen = true;

                        break;
                    }
                    default:
                    {
                        throw new NotSupportedException();
                    }
                }

                if (!readerOpen)
                {
                    this.SetOutputParameterValue(
                        this.Parameters, dbCommand.Parameters, dataContext.ContextOptions, dataContext.AdoDbSqlProvider.SqlGenerationHelper);
                }
            }
            catch (Exception ex)
            {
                if (dataContext.CurrentTransaction is AdoDbTransaction transaction && this.AddExceptionToTransaction)
                {
                    transaction.LogException(ex);
                }

                throw new DbExecuteException(ex, dbCommand.CommandText, this.GetSqlDbCode(ex));
            }
            finally
            {
                if (!readerOpen)
                {
                    dbCommand.Parameters?.Clear();
                    dbCommand.Dispose();
                }
            }

            return result;
        }

        private void SetOutputParameterValue(IEnumerable<IAdoDbParameter> adoparameters,
            IDataParameterCollection dbParameters, IDataContextOptions contextOptions, ISqlGenerationHelper helper)
        {
            var outputParameters = adoparameters.Where(m =>
                m.Direction != ParameterDirection.Input).ToList();

            foreach (var outputParameter in outputParameters)
            {
                var dbParameter = dbParameters[outputParameter.ParameterName];

                if (dbParameter == null && !String.IsNullOrWhiteSpace(outputParameter.ParameterName))
                {
                    if (outputParameter.ParameterName.StartsWith(helper.ParameterIdentifier + ""))
                    {
                        var tmpName = outputParameter.ParameterName[1..];

                        dbParameter = dbParameters[tmpName];
                    }
                }

                ((AdoDbParameter)outputParameter).Value = ((IDataParameter)dbParameter).Value;
            }
        }

        protected virtual DbCommand CreateCommand(DataContext dataContext)
        {
            this.PreCreateCommand(dataContext);

            var connection = dataContext.CurrentConnection;
            IDbCommand command = connection.DbConnection.CreateCommand();

            command.CommandText = this.SqlText;
            command.CommandType = this.CommandType;

            if (dataContext.CurrentTransaction != null)
            {
                command.Transaction = connection.CurrentTransaction.DbTransaction;
            }

            if (dataContext.ContextOptions.CommandTimeout > 0)
            {
                command.CommandTimeout = dataContext.ContextOptions.CommandTimeout;
            }

            this.CreateParameters(dataContext, command);

            return (DbCommand)command;
        }

        protected virtual void PreCreateCommand(DataContext dataContext)
        {
            // Do something driver-related, 
            // eg: replace SqlText, add Parameters for ODBC.
        }

        protected virtual void CreateParameters(DataContext dataContext, IDbCommand command)
        {
            if (this.Parameters == null)
            {
                return;
            }

            var typeMapper = dataContext.AdoDbSqlProvider.TypeMapper;

            foreach (var parameter in this.Parameters)
            {
                if (typeof(IEnumerable).IsAssignableFrom(parameter.ClrType)
                        && parameter.ClrType != typeof(string)
                        && parameter.ClrType != typeof(byte[])
                        && command.CommandType != CommandType.StoredProcedure)
                {
                    this.CreateArrayParameter(
                        command, typeMapper, parameter, (IEnumerable)parameter.Value);
                }
                else
                {
                    var typeMapping = !String.IsNullOrWhiteSpace(parameter.TypeName)
                                    ? typeMapper.FindMapping(parameter.TypeName)
                                    : typeMapper.FindMapping(parameter.ClrType);

                    if (typeMapping != null)
                    {
                        AdoDbParameterBuilder.AddDbParameter((DbCommand)command, typeMapping, parameter);
                    }
                    else
                    {
                        this.AddNoMappingParameter(command, parameter);
                    }
                }
            }
        }

        protected virtual void CreateArrayParameter(
            IDbCommand command,
            ITypeMapper typeMapper,
            IAdoDbParameter parameter,
            IEnumerable value)
        {
            command.CommandText = this.CreateArrayParameter(command.CommandText, typeMapper,
                parameter.ParameterName, value);
        }

        protected virtual void AddNoMappingParameter(IDbCommand command, IAdoDbParameter parameter)
        {
            AdoDbParameterBuilder.AddDbParameter((DbCommand)command, parameter);
        }

        protected string CreateArrayParameter(
            string sqlText,
            ITypeMapper typeMapper,
            string parameterName,
            IEnumerable value)
        {
            var args = new List<string>();

            if (value != null)
            {
                var enumerator = value.GetEnumerator();

                while (enumerator.MoveNext())
                {
                    if (enumerator.Current != null)
                    {
                        var mapping = typeMapper.FindMapping(enumerator.Current.GetType());

                        args.Add(mapping.GenerateSqlLiteral(enumerator.Current));
                    }
                    else
                    {
                        args.Add("NULL");
                    }
                }
            }
            else
            {
                args.Add("NULL");
            }

            var parameterValue = String.Join(", ", args);
            //replace
            var patten = $"(?![a-zA-Z0-9_]){ parameterName }(?![a-zA-Z0-9_])";
            var regex = new Regex(patten);

            return regex.Replace(sqlText, parameterValue);
        }

        protected virtual void ConfigDbDataReader(DbDataReader reader, IDataContextOptions options)
        {
        }

        protected virtual int GetSqlDbCode(Exception innerException)
        {
            return -1;
        }
    }
}
